package huashu.net.complain.dao;

import huashu.net.complain.databean.DeptCountDTO;
import huashu.net.complain.entity.DeptCount;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.sirdc.modules.core.dao.StringDao;

import java.util.List;

@Repository
public class DeptCountDao extends StringDao<DeptCountDao> {

    //各部门满意率
    public List<DeptCountDTO> getDeptSatify()
    {
        StringBuffer sql  = new StringBuffer();
        sql.append(" select AVG(satisfy) avg,deptname ");
        sql.append( " from t_dept_count ");
        sql.append( " GROUP BY deptname ");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(DeptCountDTO.class));
        List<DeptCountDTO> list  = (List<DeptCountDTO>) query.list();
        return list;
    }
	//各部门诉件量
    public List<DeptCountDTO> getDeptAmount()
    {
        StringBuffer sql  = new StringBuffer();
        sql.append(" select sum(allcount) sum ,deptname name ");
        sql.append(" from t_dept_count ");
        sql.append(" group by deptname");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
                query.setResultTransformer(Transformers.aliasToBean(DeptCountDTO.class));
        List<DeptCountDTO>  list = (List<DeptCountDTO>) query.list();
        return list;
    }
    //各部门及时回复率

    public List<DeptCountDTO> getDeptReponseRate()
    {

        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT AVG(ontime_response_rate) avg ,deptname ");
        sql.append(" from t_dept_count ");
        sql.append(" GROUP BY deptname ");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(DeptCountDTO.class));
        List<DeptCountDTO> list = (List<DeptCountDTO>) query.list();
         //query.list();
       return  list;

    }

    //各个部门历年满意率,回复率，查阅率
    public List<DeptCountDTO> getYearRate(String deptname)
    {
        StringBuffer sql  = new StringBuffer();
        sql.append("select deptname name,datatime, AVG(satisfy ) satifyRate, avg(ontime_read_rato) reponseRate,avg(ontime_response_rate) readRate");
        sql.append(" from t_dept_count");
        sql.append(" where 1=1");
        if(deptname!=null&&!"".equals(deptname))
        sql.append(" and deptname='"+deptname+"'");
        sql.append(" GROUP BY deptname,datatime");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(DeptCountDTO.class));
        List<DeptCountDTO> list = (List<DeptCountDTO>) query.list();
        //query.list();
        return  list;
    }

    public DeptCountDTO getdeptInfo(String deptname)
    {
        StringBuffer sql = new StringBuffer();
        sql.append("select deptname name, sum(allcount) sum ,avg(satisfy) satifyRate,avg(ontime_response_rate) reponseRate,avg(ontime_read_rato) readRate ");
        sql.append(" from t_dept_count ");
        sql.append(" where 1=1 ");

            sql.append(" and deptname ='"+deptname+"'");
        sql.append(" GROUP BY deptname ");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(DeptCountDTO.class));
        List<DeptCountDTO> list = (List<DeptCountDTO>) query.list();
        return  list.get(0);
    }

}
